package net.kts.subskill.toilet.sqlite;

import java.util.ArrayList;
import java.util.List;

import net.kts.subskill.toilet.model.ToiletVO;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ToiletDaoSQLite extends SQLiteOpenHelper {

	public ToiletDaoSQLite(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}
	
	public List<ToiletVO> selectCurrentPositionToiletList(double lat, double lng) {
		
		List<ToiletVO> toiletList = null;
		ToiletVO toiletVO = null;
		
		String id = null;
		String name = null;
		String type = null;
		String disablePerson = null;
		double tlat = 0;
		double tlng = 0;
		double comapre = 0.002;
		
		double upCmpLat = lat - comapre;
		double downCmpLat = lat + comapre;
		
		double upCmpLng = lng - comapre;
		double downCmpLng = lng + comapre;
		
		String sql = "";
		sql += " select poi_id, poi_name, etc1, etc2, x_wgs84, y_wgs84 from TBL_TOILET ";
		sql += " where ";
		sql += " y_wgs84  > " + upCmpLat;
		sql += " and y_wgs84  < " + downCmpLat;     
		sql += " and x_wgs84 > " + upCmpLng;
		sql += " and x_wgs84 < " + downCmpLng;
		
		Log.d("kts", "selectCurrentPositionToiletList ");
		Log.d("kts", "sql : " + sql);
		
		Cursor cursor = getReadableDatabase().rawQuery(sql, null);
		
		Log.d("kts", "cursor result : ");
		cursor.moveToFirst();
		Log.d("kts", "cursor count : " + cursor.getCount());
		
		while(!cursor.isAfterLast()) {
			
			if(toiletList == null) {
				toiletList = new ArrayList<ToiletVO>();
			}
			
			id = cursor.getString(0);
			name = cursor.getString(1);
			type = cursor.getString(2);
			disablePerson = cursor.getString(3);
			tlng = cursor.getDouble(4);
			tlat = cursor.getDouble(5);
			
			type = notCheck(type);
			disablePerson = notCheck(disablePerson);
			
			toiletVO = new ToiletVO();
			toiletVO.setId(id);
			toiletVO.setName(name);
			toiletVO.setType(type);
			toiletVO.setDisablePerson(disablePerson);
			toiletVO.setLat(tlat);
			toiletVO.setLng(tlng);
			
			toiletList.add(toiletVO);
			
			cursor.moveToNext();
		}
		
		
		return toiletList;
	}
	
	private String notCheck(String str) {
		
		//Log.d("kts", str);
		
		if(str.trim().equals("nothing")) {
			
			Log.d("kts", "not!! " + str);
			
			return "unknown";
		}
		
		if(str.trim().equals("��")) {
			return "����";
		}
		
		//Log.d("kts", "good!! " + str);
		
		return str;
		
	}

}
